Comparing LOD Expressions with Table Calculations in Tableau

Topic: Level of Detail (LOD) Expressions vs. Table Calculations

Objective: Understand the differences between LOD expressions and table calculations, learn their syntax, explore practical business examples, and identify the correct calculation method for different analytical requirements.

Course: JustAcademy Tableau Training

Course Demo: Register for a Tableau Course Demo

1. Introduction

Tableau provides several ways to calculate and analyze data. Two important techniques are Level of Detail (LOD) expressions and table calculations. Both help answer complex analytical questions, but they operate at different stages and serve different purposes.

LOD expressions let you specify the level of granularity at which a calculation should be performed. Table calculations transform values based on the marks and dimensions in the current visualization.

For example, if you want to calculate total sales for every customer regardless of the dimensions in a chart, you can use an LOD expression. If you want to calculate a running sales total across months displayed in a chart, a table calculation is usually the better choice.

Why Compare These Techniques?

• Choose the right calculation method for a business question.
• Understand how a calculation responds when dimensions are added or removed from a view.
• Calculate running totals, rankings, moving averages, and percentages of total.
• Calculate customer-level, order-level, and region-level metrics.
• Understand the effect of filters on calculations.
• Avoid incorrect results caused by using the wrong calculation granularity.

2. What Are LOD Expressions?

LOD stands for Level of Detail. An LOD expression allows you to calculate a measure at a specified level of granularity, which may differ from the level displayed in the visualization.

Tableau supports three main types of LOD expressions: FIXED, INCLUDE, and EXCLUDE.

General Syntax

{FIXED [Dimension] : AGGREGATION([Measure])}
{INCLUDE [Dimension] : AGGREGATION([Measure])}
{EXCLUDE [Dimension] : AGGREGATION([Measure])}

Example 1: Sales by Customer

{FIXED [Customer Name] : SUM([Sales])}

This calculates total sales for each customer, regardless of which other dimensions appear in the view. Applicable data-source, extract, and context filters can still affect the calculation.

Example 2: Sales by Region

{FIXED [Region] : SUM([Sales])}

This calculates total sales for each region. If Product Name is added to the view, the regional total may repeat for each product within that region.

When to Use LOD Expressions

• Calculate totals at a specific dimensional level.
• Calculate average customer sales within a region.
• Identify the first purchase date for each customer.
• Calculate order-level sales before averaging across orders.
• Compare detailed values with an overall total or benchmark.

3. What Are Table Calculations?

Table calculations are calculations performed on the aggregated values available in the current visualization. Their results depend on the dimensions, marks, and arrangement of data in the view.

Unlike LOD expressions, which can define a calculation at a different data granularity, table calculations work with the values returned for the visualization. They are commonly used for rankings, running totals, moving averages, differences, and percentage-of-total analysis.

Common Table Calculation Functions

Example: Running Total

RUNNING_SUM(SUM([Sales]))

This calculates a cumulative total across the marks in the order determined by the table calculation settings.

Example: Ranking Sales

RANK(SUM([Sales]))

This ranks the displayed marks according to their sales values within the selected calculation partition.

When to Use Table Calculations

• Calculate running totals across months.
• Rank products by sales.
• Calculate month-over-month differences.
• Calculate moving averages.
• Calculate percentages of total among displayed marks.
• Compare each mark with a previous or next mark in the view.

4. Key Differences Between LOD Expressions and Table Calculations

5. Understanding Calculation Granularity

Granularity means the level of detail represented by data or a calculation. Understanding granularity helps determine whether an LOD expression or table calculation is appropriate.

Example Dataset

Sales by Month

If you want to calculate total sales for each month, use an appropriate aggregation or an LOD expression when a defined calculation level is required. If you want to calculate cumulative sales from January through March, use a table calculation.

6. Example: Calculating Total Sales by Customer

Suppose a dataset contains many sales transactions for each customer. The goal is to calculate the total sales for every customer, even when the visualization includes additional dimensions.

Using an LOD Expression

{FIXED [Customer Name] : SUM([Sales])}

This calculates total sales for each customer. The calculation can remain at customer-level granularity when other dimensions are added to the view.

Why Not Use a Table Calculation?

A table calculation can summarize or compare the customer values available in a visualization, but it relies on the marks returned to the view. If customer-level values are absent or the view is aggregated at a different level, a table calculation may not provide the intended customer-level total.

Best choice: Use FIXED when you need a customer-level total that is defined independently of the dimensions in the view. Use a table calculation when you need to rank, accumulate, or compare those customer totals across the displayed marks.

7. Example: Running Total of Monthly Sales

A running total adds the current month's sales to all previous months in the calculation sequence.

Using a Table Calculation

RUNNING_SUM(SUM([Sales]))

Place Month on Columns and SUM(Sales) on Rows. Add the running-sum table calculation to the sales measure.

Expected Result

The running total depends on the order of the marks. If the month order or calculation direction changes, the result may change.

Could an LOD Expression Replace This?

A basic FIXED LOD expression can calculate sales by month, but it does not automatically accumulate earlier months. The cumulative behavior is a table calculation task because it depends on the sequence of marks in the view.

8. Example: Percentage of Total

Both LOD expressions and table calculations can be used for percentage-of-total analysis, but they answer different versions of the question.

Using a Table Calculation

SUM([Sales]) / WINDOW_SUM(SUM([Sales]))

This divides the current mark's sales by the sum of sales across the calculation window. The addressing and partitioning settings determine which marks are included in the denominator.

Using an LOD Expression

SUM([Sales]) / ATTR({FIXED : SUM([Sales])})

This divides the current view's sales by an overall FIXED total. Ordinary dimension filters may change the numerator while leaving the FIXED denominator unchanged.

Comparison

Always define what “total” means before creating the calculation. The total can mean the entire data domain, the filtered data domain, a partition, or the marks currently displayed in the view.

9. Example: Ranking Products by Sales

Suppose a dashboard displays product sales and you need to rank products from highest to lowest sales.

Using a Table Calculation

RANK(SUM([Sales]))

Place Product Name in the view and SUM(Sales) on the visualization. Add the ranking calculation and configure the sort order as required.

Using an LOD Expression

An LOD expression can calculate sales at product level:

{FIXED [Product Name] : SUM([Sales])}

This produces a product-level sales value, but it does not rank the products by itself. A table calculation such as RANK can then rank the product-level values displayed in the view.

Key Lesson

LOD expressions establish the appropriate calculation granularity. Table calculations compare or transform the resulting marks. They can be used together in the same analysis.

10. Addressing and Partitioning in Table Calculations

Table calculations use two important concepts: addressing and partitioning.

Addressing

Addressing defines the direction in which a table calculation moves through the marks. For example, a running total may move across months from left to right.

Partitioning

Partitioning defines how marks are grouped for the calculation. The calculation restarts for each partition.

Example

Imagine a chart displaying monthly sales for East and West.

• If the calculation addresses Month and partitions by Region, each region has its own running total.
• If the calculation addresses both Region and Month, the running total can continue through the combined sequence of marks, depending on the configured order.

Common Compute Using Options

How to Configure a Table Calculation

1. Add the measure to the visualization.
2. Apply a table calculation such as Running Total or Rank.
3. Right-click the calculated measure.
4. Select Edit Table Calculation.
5. Choose the appropriate Compute Using option.
6. Use Specific Dimensions when you need precise control over addressing and partitioning.
7. Check the result when you change the order or dimensions in the view.

11. Filter Behavior: LOD Expressions vs. Table Calculations

LOD expressions and table calculations can respond differently to filters because they operate at different stages.

LOD Filter Behavior

• FIXED expressions are evaluated before ordinary dimension filters.
• Context filters can affect FIXED calculations.
• INCLUDE and EXCLUDE expressions are evaluated after dimension filters.

Table Calculation Filter Behavior

Table calculations use the marks available to the calculation. A filter that removes marks before the table calculation is evaluated can change the result. A table calculation filter is applied after the calculation, so it can hide marks without removing those marks from the calculation's underlying window.

Example

Suppose a chart displays monthly sales from January through June and uses a running total.

• If a filter removes January and February before the table calculation, the running total may begin with March.
• If a table calculation filter hides January and February after the running total is calculated, the visible March value can still include January and February.

The result depends on the specific filter type and order of operations. Test filter behavior when building interactive dashboards.

12. Combining LOD Expressions and Table Calculations

LOD expressions and table calculations are not mutually exclusive. Many advanced dashboards use both to calculate detailed metrics and then analyze those metrics across a visualization.

Example: Customer Sales and Customer Ranking

Step 1: Calculate total sales for each customer.

{FIXED [Customer ID] : SUM([Sales])}

Name the field Customer Total Sales.

Step 2: Rank customers using their total sales.

RANK(SUM([Customer Total Sales]))

Depending on the view and how the FIXED result is aggregated, you may need to use an appropriate aggregation such as MIN or MAX rather than SUM. Select an aggregation that does not multiply repeated customer totals across lower-level records.

Step 3: Build the visualization.

1. Place Customer ID or Customer Name on Rows.
2. Add Customer Total Sales to Columns.
3. Add the rank calculation to Label or Tooltip.
4. Configure the ranking direction.
5. Test the results after changing dimensions and filters.

Example: Monthly Sales and Running Total

First, calculate monthly sales:

{FIXED DATETRUNC('month', [Order Date]) : SUM([Sales])}

Name the field Monthly Sales LOD. Then apply a running-sum table calculation to the monthly values in a view arranged chronologically.

When using a date expression in the FIXED declaration, ensure the date dimension used in the visualization matches the intended month-level granularity.

13. Performance Considerations

LOD expressions are generally translated into queries executed against the underlying data source, while table calculations are computed over the aggregated results available in Tableau. The best-performing approach depends on the data source, query, number of marks, and calculation complexity.

• A table calculation can be efficient when the required values are already available in the visualization.
• An LOD expression may require additional work in the underlying query, depending on the data source and calculation.
• Large views with many marks can make some table calculations expensive.
• Complex LOD expressions and joins can also affect query performance.
• Test alternative calculations when performance is slow rather than assuming one method is always faster.
• Reduce unnecessary dimensions and marks where possible.

14. Common Mistakes and Troubleshooting

15. Practical Project: Sales Performance Dashboard

Project Goal: Create a dashboard that displays customer-level sales, product rankings, monthly running totals, and percentages of total sales.

Step 1: Connect to the Dataset

Connect Tableau to a dataset containing Customer ID, Product Name, Region, Order Date, Order ID, Sales, and Profit.

Step 2: Create Customer-Level Sales

{FIXED [Customer ID] : SUM([Sales])}

Name the field Customer Total Sales.

Step 3: Create Product-Level Sales

{FIXED [Product Name] : SUM([Sales])}

Name the field Product Total Sales.

Step 4: Rank Products

RANK(SUM([Product Total Sales]))

Use the calculation to rank product marks. Validate the aggregation if the product-level total repeats across lower-level records.

Step 5: Calculate Monthly Running Sales

RUNNING_SUM(SUM([Sales]))

Place Month of Order Date on Columns and configure the table calculation to run chronologically across months.

Step 6: Calculate Percentage of Visible Total

SUM([Sales]) / WINDOW_SUM(SUM([Sales]))

Format the result as a percentage and configure the calculation's addressing and partitioning so that the denominator represents the intended group of marks.

Step 7: Add Filters

• Region filter
• Category filter
• Order Date filter
• Customer filter

Step 8: Build the Dashboard

Include a product-ranking chart, monthly sales trend, running-total line chart, and customer sales table. Add interactive filters and tooltips that explain the metrics.

Step 9: Validate the Results

1. Compare customer-level sales with a manually calculated total.
2. Check product rankings against sorted product sales.
3. Verify that running totals follow chronological order.
4. Check whether percentages use all data or only the marks in the calculation window.
5. Test the effect of each filter.

16. Practice Exercises

1. Create an LOD expression to calculate sales for each customer.
2. Create an LOD expression to calculate sales for each region.
3. Calculate a running total of sales across months.
4. Rank products from highest to lowest sales.
5. Calculate a moving average of monthly sales.
6. Calculate month-over-month sales differences.
7. Calculate each mark's percentage of total sales in a table calculation window.
8. Calculate overall sales using a FIXED LOD expression.
9. Combine customer-level sales with a table calculation to rank customers.
10. Test what happens to a running total when early months are filtered out.

Sample Answers

1. {FIXED [Customer ID] : SUM([Sales])}
2. {FIXED [Region] : SUM([Sales])}
3. RUNNING_SUM(SUM([Sales]))
4. RANK(SUM([Sales]))
5. WINDOW_AVG(SUM([Sales]), -2, 0)
6. SUM([Sales]) - LOOKUP(SUM([Sales]), -1)
7. SUM([Sales]) / WINDOW_SUM(SUM([Sales]))
8. {FIXED : SUM([Sales])}
9. {FIXED [Customer ID] : SUM([Sales])}, then rank the customer-level values with RANK()
10. Compare a normal dimension filter with a table calculation filter.

Note: The moving-average expression above uses the current mark and the two preceding marks. Month-over-month differences depend on the ordering of the marks and the table calculation settings. For exercise 9, choose an aggregation that avoids multiplying repeated customer totals.

17. Interview Questions and Answers

Q1. What is the main difference between LOD expressions and table calculations?

Answer: LOD expressions control the granularity of a calculation. Table calculations transform aggregated values based on the marks and dimensions available in the visualization.

Q2. What are the three main types of LOD expressions?

Answer: FIXED, INCLUDE, and EXCLUDE.

Q3. What is a table calculation?

Answer: A table calculation performs a calculation over values in the visualization, such as running totals, ranks, moving averages, or differences.

Q4. Which technique is generally appropriate for a running total?

Answer: A table calculation such as RUNNING_SUM(), because it depends on the order of marks in the view.

Q5. How can you calculate total sales for each customer regardless of the view's dimensions?

Answer: Use {FIXED [Customer ID] : SUM([Sales])}.

Q6. What are addressing and partitioning?

Answer: Addressing defines the direction in which a table calculation operates. Partitioning defines the groups of marks in which the calculation is performed and restarted.

Q7. Why can a table calculation change when a dimension is added to a view?

Answer: The new dimension can change the marks, addressing, partitioning, or calculation window.

Q8. Can LOD expressions and table calculations be used together?

Answer: Yes. An LOD expression can calculate a metric at a defined level, and a table calculation can rank, accumulate, or compare the resulting values across the view.

Q9. How do filters affect table calculations?

Answer: Filters that remove marks before the table calculation can change the values available to the calculation. Table calculation filters are applied after the calculation and can hide marks without removing their contribution from the calculation.

Q10. Which technique should be used to rank products by sales?

Answer: RANK() is a table calculation commonly used to rank product marks by sales.

Q11. Can a FIXED LOD expression calculate a percentage of total?

Answer: Yes. A FIXED expression can provide a total for the denominator. Its filter behavior differs from a table calculation's window-based denominator.

Q12. Which technique is better for average order value?

Answer: An order-level LOD can calculate total sales per order, after which those order totals can be averaged. The correct method depends on the data structure and the definition of an order.

18. Quick Revision Checklist

• Understand how LOD expressions control calculation granularity.
• Know the difference between FIXED, INCLUDE, and EXCLUDE.
• Understand that table calculations work with the aggregated values in the view.
• Know how to create running totals, rankings, moving averages, and differences.
• Understand addressing and partitioning.
• Know why LOD expressions and table calculations respond differently to filters.
• Use LOD expressions for defined customer, product, order, or regional calculations.
• Use table calculations for ordered comparisons and transformations across marks.
• Combine both techniques when a business question requires different levels of analysis.
• Validate the results when the view dimensions or filters change.

19. Conclusion

LOD expressions and table calculations are both powerful Tableau tools, but they serve different purposes. LOD expressions define the level of detail at which calculations are performed, while table calculations transform the aggregated values available in a visualization.

Use LOD expressions for customer-level totals, product-level metrics, order-level calculations, and other calculations that require explicit granularity. Use table calculations for running totals, rankings, moving averages, differences, and percentages based on a defined set of marks.

Understanding the differences allows you to create accurate, flexible, and interactive dashboards that answer complex business questions.

Learn more: JustAcademy Tableau Training | Register for a Tableau Course Demo

Official Tableau References

• Types of Calculations in Tableau
• Transform Values with Table Calculations
• How Level of Detail Expressions Work in Tableau
• Understanding LOD Expressions
• Create Efficient Calculations in Tableau